{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第4章 变形"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  School Class    ID Gender   Address  Height  Weight  Math Physics\n",
       "0    S_1   C_1  1101      M  street_1     173      63  34.0      A+\n",
       "1    S_1   C_1  1102      F  street_2     192      73  32.5      B+\n",
       "2    S_1   C_1  1103      M  street_2     186      82  87.2      B+\n",
       "3    S_1   C_1  1104      F  street_2     167      81  80.4      B-\n",
       "4    S_1   C_1  1105      F  street_4     159      64  84.8      B+"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>School</th>\n      <th>Class</th>\n      <th>ID</th>\n      <th>Gender</th>\n      <th>Address</th>\n      <th>Height</th>\n      <th>Weight</th>\n      <th>Math</th>\n      <th>Physics</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>S_1</td>\n      <td>C_1</td>\n      <td>1101</td>\n      <td>M</td>\n      <td>street_1</td>\n      <td>173</td>\n      <td>63</td>\n      <td>34.0</td>\n      <td>A+</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>S_1</td>\n      <td>C_1</td>\n      <td>1102</td>\n      <td>F</td>\n      <td>street_2</td>\n      <td>192</td>\n      <td>73</td>\n      <td>32.5</td>\n      <td>B+</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>S_1</td>\n      <td>C_1</td>\n      <td>1103</td>\n      <td>M</td>\n      <td>street_2</td>\n      <td>186</td>\n      <td>82</td>\n      <td>87.2</td>\n      <td>B+</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>S_1</td>\n      <td>C_1</td>\n      <td>1104</td>\n      <td>F</td>\n      <td>street_2</td>\n      <td>167</td>\n      <td>81</td>\n      <td>80.4</td>\n      <td>B-</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>S_1</td>\n      <td>C_1</td>\n      <td>1105</td>\n      <td>F</td>\n      <td>street_4</td>\n      <td>159</td>\n      <td>64</td>\n      <td>84.8</td>\n      <td>B+</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "df = pd.read_csv('data/table.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、透视表\n",
    "### 1. pivot\n",
    "df.pivot | pd.pivot :pd.pivot需要提供DataFrame变量.\n",
    "\n",
    "参数:index:行,columns:列,values:需透视的值.\n",
    "#### 一般状态下，数据在DataFrame会以压缩（stacked）状态存放，例如上面的Gender，两个类别被叠在一列中，pivot函数可将某一列作为新的cols："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender      F      M\n",
       "ID                  \n",
       "1101      NaN  173.0\n",
       "1102    192.0    NaN\n",
       "1103      NaN  186.0\n",
       "1104    167.0    NaN\n",
       "1105    159.0    NaN"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>ID</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1101</th>\n      <td>NaN</td>\n      <td>173.0</td>\n    </tr>\n    <tr>\n      <th>1102</th>\n      <td>192.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1103</th>\n      <td>NaN</td>\n      <td>186.0</td>\n    </tr>\n    <tr>\n      <th>1104</th>\n      <td>167.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1105</th>\n      <td>159.0</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "df.pivot(index='ID',columns='Gender',values='Height').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 然而pivot函数具有很强的局限性，除了功能上较少之外，还不允许values中出现重复的行列索引对（pair），例如下面的语句就会报错："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.pivot(index='School',columns='Gender',values='Height').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 因此，更多的时候会选择使用强大的pivot_table函数\n",
    "### 2. pivot_table\n",
    "df.pivot_table | pd.pivot_table :pd.pivot_table需要提供DataFrame变量.\n",
    "\n",
    "参数:index:行,columns:列,values:需透视的值,aggfunc参数是指定对values应用的聚合函数. 注意:margins:合计.是根据aggfunc指定函数的类型决定 margins:合计是什么类型的合计.\n",
    "#### 首先，再现上面的操作："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender      F      M\n",
       "ID                  \n",
       "1101      NaN  173.0\n",
       "1102    192.0    NaN\n",
       "1103      NaN  186.0\n",
       "1104    167.0    NaN\n",
       "1105    159.0    NaN"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>ID</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1101</th>\n      <td>NaN</td>\n      <td>173.0</td>\n    </tr>\n    <tr>\n      <th>1102</th>\n      <td>192.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1103</th>\n      <td>NaN</td>\n      <td>186.0</td>\n    </tr>\n    <tr>\n      <th>1104</th>\n      <td>167.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1105</th>\n      <td>159.0</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "pd.pivot_table(df,index='ID',columns='Gender',values='Height').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender           F           M\n",
       "School                        \n",
       "S_1     173.125000  178.714286\n",
       "S_2     173.727273  172.000000"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>School</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>S_1</th>\n      <td>173.125000</td>\n      <td>178.714286</td>\n    </tr>\n    <tr>\n      <th>S_2</th>\n      <td>173.727273</td>\n      <td>172.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "df.pivot_table(index='School',columns='Gender',values='Height').head()  #  对values应用的默认聚合函数是 'mean'平均数.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 由于功能更多，速度上自然是比不上原来的pivot函数："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2.19 ms ± 149 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "9.86 ms ± 95.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df.pivot(index='ID',columns='Gender',values='Height')\n",
    "%timeit pd.pivot_table(df,index='ID',columns='Gender',values='Height')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pandas中提供了各种选项，下面介绍常用参数：\n",
    "#### ① aggfunc：对组内进行聚合统计，可传入各类函数，默认为'mean'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              mean               sum      \n",
       "Gender           F           M     F     M\n",
       "School                                    \n",
       "S_1     173.125000  178.714286  1385  1251\n",
       "S_2     173.727273  172.000000  1911  1548"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">mean</th>\n      <th colspan=\"2\" halign=\"left\">sum</th>\n    </tr>\n    <tr>\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>School</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>S_1</th>\n      <td>173.125000</td>\n      <td>178.714286</td>\n      <td>1385</td>\n      <td>1251</td>\n    </tr>\n    <tr>\n      <th>S_2</th>\n      <td>173.727273</td>\n      <td>172.000000</td>\n      <td>1911</td>\n      <td>1548</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "pd.pivot_table(df,index='School',columns='Gender',values='Height',aggfunc=['mean','sum']).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ② margins：汇总边际状态"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              mean                           sum            \n",
       "Gender           F           M         All     F     M   All\n",
       "School                                                      \n",
       "S_1     173.125000  178.714286  175.733333  1385  1251  2636\n",
       "S_2     173.727273  172.000000  172.950000  1911  1548  3459\n",
       "All     173.473684  174.937500  174.142857  3296  2799  6095"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th colspan=\"3\" halign=\"left\">mean</th>\n      <th colspan=\"3\" halign=\"left\">sum</th>\n    </tr>\n    <tr>\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n      <th>All</th>\n      <th>F</th>\n      <th>M</th>\n      <th>All</th>\n    </tr>\n    <tr>\n      <th>School</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>S_1</th>\n      <td>173.125000</td>\n      <td>178.714286</td>\n      <td>175.733333</td>\n      <td>1385</td>\n      <td>1251</td>\n      <td>2636</td>\n    </tr>\n    <tr>\n      <th>S_2</th>\n      <td>173.727273</td>\n      <td>172.000000</td>\n      <td>172.950000</td>\n      <td>1911</td>\n      <td>1548</td>\n      <td>3459</td>\n    </tr>\n    <tr>\n      <th>All</th>\n      <td>173.473684</td>\n      <td>174.937500</td>\n      <td>174.142857</td>\n      <td>3296</td>\n      <td>2799</td>\n      <td>6095</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "pd.pivot_table(df,index='School',columns='Gender',values='Height',aggfunc=['mean','sum'],margins=True).head()\n",
    "#margins_name可以设置名字，默认为'All'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ③ 行、列、值都可以为多级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               Height                                                        \\\n",
       "Gender              F                                                     M   \n",
       "Address      street_1 street_2 street_4 street_5 street_6 street_7 street_1   \n",
       "School Class                                                                  \n",
       "S_1    C_1        NaN    179.5    159.0      NaN      NaN      NaN    173.0   \n",
       "       C_2        NaN      NaN    176.0    162.0    167.0      NaN      NaN   \n",
       "       C_3      175.0      NaN      NaN    187.0      NaN      NaN      NaN   \n",
       "S_2    C_1        NaN      NaN      NaN    159.0    161.0      NaN      NaN   \n",
       "       C_2        NaN      NaN      NaN      NaN      NaN    188.5    175.0   \n",
       "       C_3        NaN      NaN    157.0      NaN    164.0    190.0      NaN   \n",
       "       C_4        NaN    176.0      NaN      NaN    175.5      NaN      NaN   \n",
       "\n",
       "                                         ...   Weight                    \\\n",
       "Gender                                   ...        F                     \n",
       "Address      street_2 street_4 street_5  ... street_4 street_5 street_6   \n",
       "School Class                             ...                              \n",
       "S_1    C_1      186.0      NaN      NaN  ...     64.0      NaN      NaN   \n",
       "       C_2        NaN      NaN    188.0  ...     94.0     63.0     63.0   \n",
       "       C_3      195.0    161.0      NaN  ...      NaN     69.0      NaN   \n",
       "S_2    C_1        NaN    163.5      NaN  ...      NaN     97.0     61.0   \n",
       "       C_2        NaN    155.0    193.0  ...      NaN      NaN      NaN   \n",
       "       C_3        NaN    187.0    171.0  ...     78.0      NaN     81.0   \n",
       "       C_4        NaN      NaN      NaN  ...      NaN      NaN     57.0   \n",
       "\n",
       "                                                                             \n",
       "Gender                       M                                               \n",
       "Address      street_7 street_1 street_2 street_4 street_5 street_6 street_7  \n",
       "School Class                                                                 \n",
       "S_1    C_1        NaN     63.0     82.0      NaN      NaN      NaN      NaN  \n",
       "       C_2        NaN      NaN      NaN      NaN     68.0     53.0      NaN  \n",
       "       C_3        NaN      NaN     70.0     68.0      NaN      NaN     82.0  \n",
       "S_2    C_1        NaN      NaN      NaN     71.0      NaN      NaN     84.0  \n",
       "       C_2       76.5     74.0      NaN     91.0    100.0      NaN      NaN  \n",
       "       C_3       99.0      NaN      NaN     73.0     88.0      NaN      NaN  \n",
       "       C_4        NaN      NaN      NaN      NaN      NaN      NaN     82.0  \n",
       "\n",
       "[7 rows x 24 columns]"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"10\" halign=\"left\">Height</th>\n      <th>...</th>\n      <th colspan=\"10\" halign=\"left\">Weight</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th>Gender</th>\n      <th colspan=\"6\" halign=\"left\">F</th>\n      <th colspan=\"4\" halign=\"left\">M</th>\n      <th>...</th>\n      <th colspan=\"4\" halign=\"left\">F</th>\n      <th colspan=\"6\" halign=\"left\">M</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th>Address</th>\n      <th>street_1</th>\n      <th>street_2</th>\n      <th>street_4</th>\n      <th>street_5</th>\n      <th>street_6</th>\n      <th>street_7</th>\n      <th>street_1</th>\n      <th>street_2</th>\n      <th>street_4</th>\n      <th>street_5</th>\n      <th>...</th>\n      <th>street_4</th>\n      <th>street_5</th>\n      <th>street_6</th>\n      <th>street_7</th>\n      <th>street_1</th>\n      <th>street_2</th>\n      <th>street_4</th>\n      <th>street_5</th>\n      <th>street_6</th>\n      <th>street_7</th>\n    </tr>\n    <tr>\n      <th>School</th>\n      <th>Class</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">S_1</th>\n      <th>C_1</th>\n      <td>NaN</td>\n      <td>179.5</td>\n      <td>159.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>173.0</td>\n      <td>186.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>64.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>63.0</td>\n      <td>82.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>C_2</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>176.0</td>\n      <td>162.0</td>\n      <td>167.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>188.0</td>\n      <td>...</td>\n      <td>94.0</td>\n      <td>63.0</td>\n      <td>63.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>68.0</td>\n      <td>53.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>C_3</th>\n      <td>175.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>187.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>195.0</td>\n      <td>161.0</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>69.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>70.0</td>\n      <td>68.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>82.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">S_2</th>\n      <th>C_1</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>159.0</td>\n      <td>161.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>163.5</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>97.0</td>\n      <td>61.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>71.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>84.0</td>\n    </tr>\n    <tr>\n      <th>C_2</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>188.5</td>\n      <td>175.0</td>\n      <td>NaN</td>\n      <td>155.0</td>\n      <td>193.0</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>76.5</td>\n      <td>74.0</td>\n      <td>NaN</td>\n      <td>91.0</td>\n      <td>100.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>C_3</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>157.0</td>\n      <td>NaN</td>\n      <td>164.0</td>\n      <td>190.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>187.0</td>\n      <td>171.0</td>\n      <td>...</td>\n      <td>78.0</td>\n      <td>NaN</td>\n      <td>81.0</td>\n      <td>99.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>73.0</td>\n      <td>88.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>C_4</th>\n      <td>NaN</td>\n      <td>176.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>175.5</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>57.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>82.0</td>\n    </tr>\n  </tbody>\n</table>\n<p>7 rows × 24 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "pd.pivot_table(df,index=['School','Class'],\n",
    "               columns=['Gender','Address'],\n",
    "               values=['Height','Weight'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. crosstab（交叉表）\n",
    "#### 交叉表是一种特殊的透视表，典型的用途如分组统计，如现在想要统计关于街道和性别分组的频数："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender    F  M\n",
       "Address       \n",
       "street_1  1  2\n",
       "street_2  4  2\n",
       "street_4  3  5\n",
       "street_5  3  3\n",
       "street_6  5  1\n",
       "street_7  3  3"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>Address</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>street_1</th>\n      <td>1</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>street_2</th>\n      <td>4</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>street_4</th>\n      <td>3</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>street_5</th>\n      <td>3</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>street_6</th>\n      <td>5</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>street_7</th>\n      <td>3</td>\n      <td>3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "pd.crosstab(index=df['Address'],columns=df['Gender'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 交叉表的功能也很强大（但目前还不支持多级分组），下面说明一些重要参数：\n",
    "#### ① values和aggfunc：分组对某些数据进行聚合操作，这两个参数必须成对出现"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender     F   M\n",
       "Address         \n",
       "street_1  18   6\n",
       "street_2   1   1\n",
       "street_4   3   3\n",
       "street_5   9   9\n",
       "street_6   9   6\n",
       "street_7   1  11"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>Address</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>street_1</th>\n      <td>18</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>street_2</th>\n      <td>1</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>street_4</th>\n      <td>3</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>street_5</th>\n      <td>9</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>street_6</th>\n      <td>9</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>street_7</th>\n      <td>1</td>\n      <td>11</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "pd.crosstab(index=df['Address'],columns=df['Gender'],\n",
    "            values=np.random.randint(1,20,df.shape[0]),aggfunc='min')\n",
    "#默认参数等于如下方法：\n",
    "#pd.crosstab(index=df['Address'],columns=df['Gender'],values=1,aggfunc='count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ② 除了边际参数margins外，还引入了normalize参数，可选'all','index','columns'参数值\n",
    "        normalize(百份比值)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender           F         M       All\n",
       "Address                               \n",
       "street_1  0.028571  0.057143  0.085714\n",
       "street_2  0.114286  0.057143  0.171429\n",
       "street_4  0.085714  0.142857  0.228571\n",
       "street_5  0.085714  0.085714  0.171429\n",
       "street_6  0.142857  0.028571  0.171429\n",
       "street_7  0.085714  0.085714  0.171429\n",
       "All       0.542857  0.457143  1.000000"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n      <th>All</th>\n    </tr>\n    <tr>\n      <th>Address</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>street_1</th>\n      <td>0.028571</td>\n      <td>0.057143</td>\n      <td>0.085714</td>\n    </tr>\n    <tr>\n      <th>street_2</th>\n      <td>0.114286</td>\n      <td>0.057143</td>\n      <td>0.171429</td>\n    </tr>\n    <tr>\n      <th>street_4</th>\n      <td>0.085714</td>\n      <td>0.142857</td>\n      <td>0.228571</td>\n    </tr>\n    <tr>\n      <th>street_5</th>\n      <td>0.085714</td>\n      <td>0.085714</td>\n      <td>0.171429</td>\n    </tr>\n    <tr>\n      <th>street_6</th>\n      <td>0.142857</td>\n      <td>0.028571</td>\n      <td>0.171429</td>\n    </tr>\n    <tr>\n      <th>street_7</th>\n      <td>0.085714</td>\n      <td>0.085714</td>\n      <td>0.171429</td>\n    </tr>\n    <tr>\n      <th>All</th>\n      <td>0.542857</td>\n      <td>0.457143</td>\n      <td>1.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "pd.crosstab(index=df['Address'],columns=df['Gender'],normalize='all',margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、其他变形方法\n",
    "### 1. melt\n",
    "#### melt函数可以认为是pivot函数的逆操作，将unstacked状态的数据，压缩成stacked，使“宽”的DataFrame变“窄”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     ID Gender  Math\n",
       "0  1101      M  34.0\n",
       "1  1102      F  32.5\n",
       "2  1103      M  87.2\n",
       "3  1104      F  80.4\n",
       "4  1105      F  84.8"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>ID</th>\n      <th>Gender</th>\n      <th>Math</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1101</td>\n      <td>M</td>\n      <td>34.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1102</td>\n      <td>F</td>\n      <td>32.5</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1103</td>\n      <td>M</td>\n      <td>87.2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1104</td>\n      <td>F</td>\n      <td>80.4</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1105</td>\n      <td>F</td>\n      <td>84.8</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "source": [
    "df_m = df[['ID','Gender','Math']]\n",
    "df_m.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender     F     M\n",
       "ID                \n",
       "1101     NaN  34.0\n",
       "1102    32.5   NaN\n",
       "1103     NaN  87.2\n",
       "1104    80.4   NaN\n",
       "1105    84.8   NaN"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>ID</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1101</th>\n      <td>NaN</td>\n      <td>34.0</td>\n    </tr>\n    <tr>\n      <th>1102</th>\n      <td>32.5</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1103</th>\n      <td>NaN</td>\n      <td>87.2</td>\n    </tr>\n    <tr>\n      <th>1104</th>\n      <td>80.4</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1105</th>\n      <td>84.8</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "source": [
    "df.pivot(index='ID',columns='Gender',values='Math').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### melt函数中的id_vars表示需要保留的列，value_vars表示需要stack的一组列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "source": [
    "pivoted = df.pivot(index='ID',columns='Gender',values='Math')\n",
    "result = pivoted.reset_index().melt(id_vars=['ID'],value_vars=['F','M'],value_name='Math')\\\n",
    "                     .dropna().set_index('ID').sort_index()\n",
    "#检验是否与展开前的df相同，可以分别将这些链式方法的中间步骤展开，看看是什么结果\n",
    "result.equals(df_m.set_index('ID'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     Gender  Math\n",
       "ID               \n",
       "1101      M  34.0\n",
       "1102      F  32.5\n",
       "1103      M  87.2\n",
       "1104      F  80.4\n",
       "1105      F  84.8"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Gender</th>\n      <th>Math</th>\n    </tr>\n    <tr>\n      <th>ID</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1101</th>\n      <td>M</td>\n      <td>34.0</td>\n    </tr>\n    <tr>\n      <th>1102</th>\n      <td>F</td>\n      <td>32.5</td>\n    </tr>\n    <tr>\n      <th>1103</th>\n      <td>M</td>\n      <td>87.2</td>\n    </tr>\n    <tr>\n      <th>1104</th>\n      <td>F</td>\n      <td>80.4</td>\n    </tr>\n    <tr>\n      <th>1105</th>\n      <td>F</td>\n      <td>84.8</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "source": [
    "pivoted.reset_index().melt(id_vars=['ID'],value_vars=['F','M'],value_name='Math')\\\n",
    "                     .dropna().set_index('ID').sort_index().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 压缩与展开\n",
    "#### （1）stack：这是最基础的变形函数，总共只有两个参数：level和dropna\n",
    "            stack:将列索引堆叠到行索引,默认堆叠最内层列索引."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "           Height        Weight      \n",
       "Gender          F      M      F     M\n",
       "Class ID                             \n",
       "C_1   1101    NaN  173.0    NaN  63.0\n",
       "      1102  192.0    NaN   73.0   NaN\n",
       "C_2   1201    NaN  188.0    NaN  68.0\n",
       "      1202  176.0    NaN   94.0   NaN\n",
       "C_3   1301    NaN  161.0    NaN  68.0\n",
       "      1302  175.0    NaN   57.0   NaN\n",
       "C_4   2401  192.0    NaN   62.0   NaN\n",
       "      2402    NaN  166.0    NaN  82.0"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">Height</th>\n      <th colspan=\"2\" halign=\"left\">Weight</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>Class</th>\n      <th>ID</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_1</th>\n      <th>1101</th>\n      <td>NaN</td>\n      <td>173.0</td>\n      <td>NaN</td>\n      <td>63.0</td>\n    </tr>\n    <tr>\n      <th>1102</th>\n      <td>192.0</td>\n      <td>NaN</td>\n      <td>73.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_2</th>\n      <th>1201</th>\n      <td>NaN</td>\n      <td>188.0</td>\n      <td>NaN</td>\n      <td>68.0</td>\n    </tr>\n    <tr>\n      <th>1202</th>\n      <td>176.0</td>\n      <td>NaN</td>\n      <td>94.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_3</th>\n      <th>1301</th>\n      <td>NaN</td>\n      <td>161.0</td>\n      <td>NaN</td>\n      <td>68.0</td>\n    </tr>\n    <tr>\n      <th>1302</th>\n      <td>175.0</td>\n      <td>NaN</td>\n      <td>57.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_4</th>\n      <th>2401</th>\n      <td>192.0</td>\n      <td>NaN</td>\n      <td>62.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2402</th>\n      <td>NaN</td>\n      <td>166.0</td>\n      <td>NaN</td>\n      <td>82.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "df_s = pd.pivot_table(df,index=['Class','ID'],columns='Gender',values=['Height','Weight'])\n",
    "df_s.groupby('Class').head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "           Height        Weight      \n",
       "Gender          F      M      F     M\n",
       "Class ID                             \n",
       "C_1   1101    NaN  173.0    NaN  63.0\n",
       "      1102  192.0    NaN   73.0   NaN"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">Height</th>\n      <th colspan=\"2\" halign=\"left\">Weight</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>Class</th>\n      <th>ID</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_1</th>\n      <th>1101</th>\n      <td>NaN</td>\n      <td>173.0</td>\n      <td>NaN</td>\n      <td>63.0</td>\n    </tr>\n    <tr>\n      <th>1102</th>\n      <td>192.0</td>\n      <td>NaN</td>\n      <td>73.0</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "source": [
    "df_s.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                   Height  Weight\n",
       "Class ID   Gender                \n",
       "C_1   1101 M        173.0    63.0\n",
       "      1102 F        192.0    73.0\n",
       "C_2   1201 M        188.0    68.0\n",
       "      1202 F        176.0    94.0\n",
       "C_3   1301 M        161.0    68.0\n",
       "      1302 F        175.0    57.0\n",
       "C_4   2401 F        192.0    62.0\n",
       "      2402 M        166.0    82.0"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th></th>\n      <th>Height</th>\n      <th>Weight</th>\n    </tr>\n    <tr>\n      <th>Class</th>\n      <th>ID</th>\n      <th>Gender</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_1</th>\n      <th>1101</th>\n      <th>M</th>\n      <td>173.0</td>\n      <td>63.0</td>\n    </tr>\n    <tr>\n      <th>1102</th>\n      <th>F</th>\n      <td>192.0</td>\n      <td>73.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_2</th>\n      <th>1201</th>\n      <th>M</th>\n      <td>188.0</td>\n      <td>68.0</td>\n    </tr>\n    <tr>\n      <th>1202</th>\n      <th>F</th>\n      <td>176.0</td>\n      <td>94.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_3</th>\n      <th>1301</th>\n      <th>M</th>\n      <td>161.0</td>\n      <td>68.0</td>\n    </tr>\n    <tr>\n      <th>1302</th>\n      <th>F</th>\n      <td>175.0</td>\n      <td>57.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_4</th>\n      <th>2401</th>\n      <th>F</th>\n      <td>192.0</td>\n      <td>62.0</td>\n    </tr>\n    <tr>\n      <th>2402</th>\n      <th>M</th>\n      <td>166.0</td>\n      <td>82.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "df_stacked = df_s.stack()\n",
    "df_stacked.groupby('Class').head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### stack函数可以看做将横向的索引放到纵向，因此功能类似与melt，参数level可指定变化的列索引是哪一层（或哪几层，需要列表）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender                 F      M\n",
       "Class ID                       \n",
       "C_1   1101 Height    NaN  173.0\n",
       "           Weight    NaN   63.0\n",
       "C_2   1201 Height    NaN  188.0\n",
       "           Weight    NaN   68.0\n",
       "C_3   1301 Height    NaN  161.0\n",
       "           Weight    NaN   68.0\n",
       "C_4   2401 Height  192.0    NaN\n",
       "           Weight   62.0    NaN"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>Class</th>\n      <th>ID</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_1</th>\n      <th rowspan=\"2\" valign=\"top\">1101</th>\n      <th>Height</th>\n      <td>NaN</td>\n      <td>173.0</td>\n    </tr>\n    <tr>\n      <th>Weight</th>\n      <td>NaN</td>\n      <td>63.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_2</th>\n      <th rowspan=\"2\" valign=\"top\">1201</th>\n      <th>Height</th>\n      <td>NaN</td>\n      <td>188.0</td>\n    </tr>\n    <tr>\n      <th>Weight</th>\n      <td>NaN</td>\n      <td>68.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_3</th>\n      <th rowspan=\"2\" valign=\"top\">1301</th>\n      <th>Height</th>\n      <td>NaN</td>\n      <td>161.0</td>\n    </tr>\n    <tr>\n      <th>Weight</th>\n      <td>NaN</td>\n      <td>68.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">C_4</th>\n      <th rowspan=\"2\" valign=\"top\">2401</th>\n      <th>Height</th>\n      <td>192.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>Weight</th>\n      <td>62.0</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "df_stacked = df_s.stack(0)\n",
    "df_stacked.groupby('Class').head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2) unstack：stack的逆函数，功能上类似于pivot_table\n",
    "        unstack:将行索引拆堆到列索引,默认拆堆最内层行索引."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Gender                 F      M\n",
       "Class ID                       \n",
       "C_1   1101 Height    NaN  173.0\n",
       "           Weight    NaN   63.0\n",
       "      1102 Height  192.0    NaN\n",
       "           Weight   73.0    NaN\n",
       "      1103 Height    NaN  186.0"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>Gender</th>\n      <th>F</th>\n      <th>M</th>\n    </tr>\n    <tr>\n      <th>Class</th>\n      <th>ID</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"5\" valign=\"top\">C_1</th>\n      <th rowspan=\"2\" valign=\"top\">1101</th>\n      <th>Height</th>\n      <td>NaN</td>\n      <td>173.0</td>\n    </tr>\n    <tr>\n      <th>Weight</th>\n      <td>NaN</td>\n      <td>63.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">1102</th>\n      <th>Height</th>\n      <td>192.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>Weight</th>\n      <td>73.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1103</th>\n      <th>Height</th>\n      <td>NaN</td>\n      <td>186.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 49
    }
   ],
   "source": [
    "df_stacked.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "metadata": {},
     "execution_count": 50
    }
   ],
   "source": [
    "result = df_stacked.unstack().swaplevel(1,0,axis=1).sort_index(axis=1)\n",
    "result.equals(df_s)\n",
    "#同样在unstack中可以指定level参数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、哑变量与因子化\n",
    "### 1. Dummy Variable（哑变量）\n",
    "#### 这里主要介绍get_dummies函数，其功能主要是进行one-hot编码："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Class</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C_1</td>\n",
       "      <td>M</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C_1</td>\n",
       "      <td>F</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C_1</td>\n",
       "      <td>M</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C_1</td>\n",
       "      <td>F</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C_1</td>\n",
       "      <td>F</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Class Gender  Weight\n",
       "0   C_1      M      63\n",
       "1   C_1      F      73\n",
       "2   C_1      M      82\n",
       "3   C_1      F      81\n",
       "4   C_1      F      64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_d = df[['Class','Gender','Weight']]\n",
    "df_d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 现在希望将上面的表格前两列转化为哑变量，并加入第三列Weight数值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Class_C_1</th>\n",
       "      <th>Class_C_2</th>\n",
       "      <th>Class_C_3</th>\n",
       "      <th>Class_C_4</th>\n",
       "      <th>Gender_F</th>\n",
       "      <th>Gender_M</th>\n",
       "      <th>Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Class_C_1  Class_C_2  Class_C_3  Class_C_4  Gender_F  Gender_M  Weight\n",
       "0          1          0          0          0         0         1      63\n",
       "1          1          0          0          0         1         0      73\n",
       "2          1          0          0          0         0         1      82\n",
       "3          1          0          0          0         1         0      81\n",
       "4          1          0          0          0         1         0      64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.get_dummies(df_d[['Class','Gender']]).join(df_d['Weight']).head()\n",
    "#可选prefix参数添加前缀，prefix_sep添加分隔符"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. factorize方法\n",
    "#### 该方法主要用于自然数编码，并且缺失值会被记做-1，其中sort参数表示是否排序后赋值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1, -1,  0,  2,  1])"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "array(['a', 'b', 'c'], dtype=object)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "codes, uniques = pd.factorize(['b', None, 'a', 'c', 'b'], sort=True)\n",
    "display(codes)\n",
    "display(uniques)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、问题与练习"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 问题\n",
    "#### 【问题一】 上面提到了许多变形函数，如melt/crosstab/pivot/pivot_table/stack/unstack函数，请总结它们各自的使用特点。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【问题二】 变形函数和多级索引是什么关系？哪些变形函数会使得索引维数变化？具体如何变化？\n",
    "#### 【问题三】 请举出一个除了上文提过的关于哑变量方法的例子。\n",
    "#### 【问题四】 使用完stack后立即使用unstack一定能保证变化结果与原始表完全一致吗？\n",
    "#### 【问题五】 透视表中涉及了三个函数，请分别使用它们完成相同的目标（任务自定）并比较哪个速度最快。\n",
    "#### 【问题六】 既然melt起到了stack的功能，为什么再设计stack函数？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 练习\n",
    "#### 【练习一】 继续使用上一章的药物数据集："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>YYYY</th>\n",
       "      <th>State</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>SubstanceName</th>\n",
       "      <th>DrugReports</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>VA</td>\n",
       "      <td>ACCOMACK</td>\n",
       "      <td>Propoxyphene</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>OH</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>Morphine</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010</td>\n",
       "      <td>PA</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010</td>\n",
       "      <td>VA</td>\n",
       "      <td>ALEXANDRIA CITY</td>\n",
       "      <td>Heroin</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "      <td>PA</td>\n",
       "      <td>ALLEGHENY</td>\n",
       "      <td>Hydromorphone</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   YYYY State           COUNTY  SubstanceName  DrugReports\n",
       "0  2010    VA         ACCOMACK   Propoxyphene            1\n",
       "1  2010    OH            ADAMS       Morphine            9\n",
       "2  2010    PA            ADAMS      Methadone            2\n",
       "3  2010    VA  ALEXANDRIA CITY         Heroin            5\n",
       "4  2010    PA        ALLEGHENY  Hydromorphone            5"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/Drugs.csv').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (a) 现在请你将数据表转化成如下形态，每行需要显示每种药物在每个地区的10年至17年的变化情况，且前三列需要排序：\n",
    "![avatar](picture/drug_pic.png)\n",
    "#### (b) 现在请将(a)中的结果恢复到原数据表，并通过equal函数检验初始表与新的结果是否一致（返回True）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【练习二】 现有一份关于某地区地震情况的数据集，请解决如下问题："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>时间</th>\n",
       "      <th>维度</th>\n",
       "      <th>经度</th>\n",
       "      <th>方向</th>\n",
       "      <th>距离</th>\n",
       "      <th>深度</th>\n",
       "      <th>烈度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2003.05.20</td>\n",
       "      <td>12:17:44 AM</td>\n",
       "      <td>39.04</td>\n",
       "      <td>40.38</td>\n",
       "      <td>west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007.08.01</td>\n",
       "      <td>12:03:08 AM</td>\n",
       "      <td>40.79</td>\n",
       "      <td>30.09</td>\n",
       "      <td>west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.2</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1978.05.07</td>\n",
       "      <td>12:41:37 AM</td>\n",
       "      <td>38.58</td>\n",
       "      <td>27.61</td>\n",
       "      <td>south_west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1997.03.22</td>\n",
       "      <td>12:31:45 AM</td>\n",
       "      <td>39.47</td>\n",
       "      <td>36.44</td>\n",
       "      <td>south_west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000.04.02</td>\n",
       "      <td>12:57:38 AM</td>\n",
       "      <td>40.80</td>\n",
       "      <td>30.24</td>\n",
       "      <td>south_west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           日期           时间     维度     经度          方向   距离    深度   烈度\n",
       "0  2003.05.20  12:17:44 AM  39.04  40.38        west  0.1  10.0  0.0\n",
       "1  2007.08.01  12:03:08 AM  40.79  30.09        west  0.1   5.2  4.0\n",
       "2  1978.05.07  12:41:37 AM  38.58  27.61  south_west  0.1   0.0  0.0\n",
       "3  1997.03.22  12:31:45 AM  39.47  36.44  south_west  0.1  10.0  0.0\n",
       "4  2000.04.02  12:57:38 AM  40.80  30.24  south_west  0.1   7.0  0.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/Earthquake.csv').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (a) 现在请你将数据表转化成如下形态，将方向列展开，并将距离、深度和烈度三个属性压缩：\n",
    "![avatar](picture/earthquake_pic.png)\n",
    "#### (b) 现在请将(a)中的结果恢复到原数据表，并通过equal函数检验初始表与新的结果是否一致（返回True）"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python373jvsc74a57bd0af09bc94d41e018aa4bb791c06386d7d2a0d085b02fa573368449120a8cb3c6e",
   "display_name": "Python 3.7.3 64-bit ('base': conda)"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}